package com.ifox.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Vector;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.ifox.entity.IFox;

public class BaseService {
	private Logger logger = LoggerFactory.getLogger(getClass());
	
	public void exchange(IFox ifox) {
		
	}

	public String getTestConnectionResult(IFox ifox){
		String result = "";
		Connection srcConn = null;
		Connection targetConn = null;
		String srcDriver = ifox.getDriverClass(ifox.getSrcConnType());
		String targetDriver = ifox.getDriverClass(ifox.getTargetConnType());
		String srcUrl = ifox.getConnStr(IFox.SRC);
		String targetUrl = ifox.getConnStr(IFox.TARGET);
		try {
			Class.forName(srcDriver);
			srcConn = DriverManager.getConnection(srcUrl, ifox.getSrcUsername(), ifox.getSrcPassword());
			PreparedStatement p = srcConn.prepareStatement(ifox.getSrcTestSql());
			p.setQueryTimeout(10);
			p.execute();
			result = "连接源数据库【成功】\n";
		} catch (Exception e) {
			logger.error(e.getMessage(),e);
			result = "连接源数据库【失败】\n";
		} finally{
			try {
				srcConn.close();
			} catch (SQLException e) {
				logger.error(e.getMessage(),e);
			}
		}
		
		try {
			Class.forName(targetDriver);
			targetConn = DriverManager.getConnection(targetUrl, ifox.getTargetUsername(), ifox.getTargetPassword());
			PreparedStatement p = targetConn.prepareStatement(ifox.getTargetTestSql());
			p.setQueryTimeout(10);
			p.execute();
			result += "连接目标数据库【成功】";
		} catch (Exception e) {
			logger.error(e.getMessage(),e);
			result += "连接目标数据库【失败】";
		} finally{
			try {
				targetConn.close();
			} catch (SQLException e) {
				logger.error(e.getMessage(),e);
			}
		}
		return result;
	}

	public boolean testConn(IFox ifox){
		boolean flag = true;
		Connection srcConn = null;
		Connection targetConn = null;
		String srcDriver = ifox.getDriverClass(ifox.getSrcConnType());
		String targetDriver = ifox.getDriverClass(ifox.getTargetConnType());
		String srcUrl = ifox.getConnStr(IFox.SRC);
		String targetUrl = ifox.getConnStr(IFox.TARGET);
		
		try {
			Class.forName(srcDriver);
			srcConn = DriverManager.getConnection(srcUrl, ifox.getSrcUsername(), ifox.getSrcPassword());
			srcConn.prepareStatement(ifox.getSrcTestSql()).executeQuery();
		} catch (Exception e) {
			logger.error(e.getMessage(),e);
			flag = false;
		} finally{
			try {
				srcConn.close();
			} catch (SQLException e) {
				logger.error(e.getMessage(),e);
			}
		}
		
		try {
			Class.forName(targetDriver);
			targetConn = DriverManager.getConnection(targetUrl, ifox.getTargetUsername(), ifox.getTargetPassword());
			targetConn.prepareStatement(ifox.getTargetTestSql()).executeQuery();
		} catch (Exception e) {
			logger.error(e.getMessage(),e);
			flag = false;
		} finally{
			try {
				targetConn.close();
			} catch (SQLException e) {
				logger.error(e.getMessage(),e);
			}
		}
		return flag;
	}

	public Vector<String> getSrcTable(IFox ifox){
		Connection srcConn = null;
		String srcDriver = ifox.getDriverClass(ifox.getSrcConnType());
		String srcUrl = ifox.getConnStr(IFox.SRC);
		ResultSet resultSet = null;
		String sql = "";
		Vector<String> tables = new Vector<String>();
		tables.add("======请选择======");
		try {
			Class.forName(srcDriver);
			srcConn = DriverManager.getConnection(srcUrl, ifox.getSrcUsername(), ifox.getSrcPassword());
			if("SQLServer2008".equals(ifox.getSrcConnType())){
				sql = "select name as tablename from sys.tables order by name";
			}else if("Oracle10g".equals(ifox.getSrcConnType())){
				sql = "select table_name as tablename from user_tables order by table_name";
			}
			resultSet = srcConn.prepareStatement(sql).executeQuery();  
			while(resultSet.next()){
				tables.add(resultSet.getString("tablename"));
			}
		} catch (Exception e) {
			logger.error(e.getMessage(),e);
		} finally{
			try {
				resultSet.close();
				srcConn.close();
			} catch (SQLException e) {
				logger.error(e.getMessage(),e);
			}
		}
		return tables;
	}

	public Vector<String> getTargetTable(IFox ifox){
		Connection targetConn = null;
		String targetDriver = ifox.getDriverClass(ifox.getTargetConnType());
		String targetUrl = ifox.getConnStr(IFox.TARGET);
		ResultSet resultSet = null;
		String sql = "";
		Vector<String> tables = new Vector<String>();
		tables.add("======请选择======");
		try {
			Class.forName(targetDriver);
			targetConn = DriverManager.getConnection(targetUrl, ifox.getTargetUsername(), ifox.getTargetPassword());
			if("SQLServer2008".equals(ifox.getTargetConnType())){
				sql = "select name as tablename from sys.tables order by name";
			}else if("Oracle10g".equals(ifox.getTargetConnType())){
				sql = "select table_name as tablename from user_tables order by table_name";
			}
			resultSet = targetConn.prepareStatement(sql).executeQuery();  
			while(resultSet.next()){
				tables.add(resultSet.getString("tablename"));
			}
		} catch (Exception e) {
			logger.error(e.getMessage(),e);
		} finally{
			try {
				resultSet.close();
				targetConn.close();
			} catch (SQLException e) {
				logger.error(e.getMessage(),e);
			}
		}
		return tables;
	}

	public Vector<String> getColumnsFromSrcTable(IFox ifox,String tableName) {
		Connection srcConn = null;
		String srcDriver = ifox.getDriverClass(ifox.getSrcConnType());
		String srcUrl = ifox.getConnStr(IFox.SRC);
		ResultSet resultSet = null;
		
		Vector<String> columns = new Vector<String>();
		try {
			Class.forName(srcDriver);
			srcConn = DriverManager.getConnection(srcUrl, ifox.getSrcUsername(), ifox.getSrcPassword());
			String sql = "select * from " + tableName + " where 1=2";
			PreparedStatement pstmt = srcConn.prepareStatement(sql);
			resultSet = pstmt.executeQuery();
			ResultSetMetaData rsmd =resultSet.getMetaData();
			int tableLength = rsmd.getColumnCount();  
			for (int i = 1; i <= tableLength; i++) {
				String columnName = rsmd.getColumnName(i);
				columns.add(columnName);
			}
		} catch (Exception e) {
			logger.error(e.getMessage(),e);
		} finally{
			try {
				resultSet.close();
				srcConn.close();
			} catch (Exception e) {
				logger.error(e.getMessage(),e);
			}
		}
		return columns;
	}
	
	public Vector<String> getColumnsFromTargetTable(IFox ifox,String tableName) throws Exception {
		Connection targetConn = null;
		String targetDriver = ifox.getDriverClass(ifox.getTargetConnType());
		String targetUrl = ifox.getConnStr(IFox.TARGET);
		ResultSet resultSet = null;
		
		Vector<String> columns = new Vector<String>();
		try {
			Class.forName(targetDriver);
			targetConn = DriverManager.getConnection(targetUrl, ifox.getTargetUsername(), ifox.getTargetPassword());
			String sql = "select * from " + tableName + " where 1=2";
			PreparedStatement pstmt = targetConn.prepareStatement(sql);
			resultSet = pstmt.executeQuery();
			ResultSetMetaData rsmd =resultSet.getMetaData();
			int tableLength = rsmd.getColumnCount();  
			for (int i = 1; i <= tableLength; i++) {
				String columnName = rsmd.getColumnName(i);
				columns.add(columnName);
			}
		} catch (Exception e) {
			logger.error(e.getMessage(),e);
		} finally{
			try {
				resultSet.close();
				targetConn.close();
			} catch (Exception e) {
				logger.error(e.getMessage(),e);
			}
		}
		return columns;
	}
}
